﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BLL.Services.Televisors.Listing;

namespace BLL.Services.Televisors
{
    public class TelevisorsCollection
    {
        private readonly string _connectionString;

        public TelevisorsCollection(string connectionString)
        {
            _connectionString = connectionString;
        }

        public void Create(string name, int type, decimal diagonal, bool treeD, DateTime releaseDate, string avatar, int magazineId)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                var cmd = StoredProcedures.Televisors.Insert(conn,name,type,diagonal,treeD,releaseDate,avatar,magazineId);

                cmd.ExecuteNonQuery();
            }
        }

        public void Delete(int televisorId)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                var cmd = StoredProcedures.Televisors.Delete(conn, televisorId);

                cmd.ExecuteNonQuery();
            }
        }

        public void Save(int televisorId,string name, int type, decimal diagonal, bool treeD, DateTime releaseDate,string avatar,int magazineId)
        {
            using (var conn=new SqlConnection(_connectionString))
            {
                conn.Open();

                var cmd = StoredProcedures.Televisors.Update(conn, televisorId, name, type, diagonal, treeD, releaseDate, avatar,magazineId);

                cmd.ExecuteNonQuery();
            }
        }

        public TelevisorsListing Listing(int page, int size, string column, int direction, TelevisorsFilter filter)
        {
            var televisorsListing = new TelevisorsListing();
            if (filter == null)
            {
                filter = new TelevisorsFilter();
            }

            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                televisorsListing.Page = page;
                televisorsListing.Size = size;
                televisorsListing.Column = column;
                televisorsListing.Direction = direction;
                televisorsListing.Filter = filter;

                var cmd = StoredProcedures.Televisors.Listing(conn, page, size, column, direction, filter.Name);

                var totalCount = new SqlParameter("@TotalCount", SqlDbType.Int, 4)
                    {
                        Direction = ParameterDirection.ReturnValue
                    };
                cmd.Parameters.Add(totalCount);

                using (var reader =cmd.ExecuteReader() )
                {
                    while (reader.Read())
                    {
                        televisorsListing.Televisors.Add(new TelevisorsListing.Televisor
                            {
                                TelevisorId = reader.GetInt32(0),
                                Name = reader[1] as string,
                                TypeInt = reader.GetInt32(2),
                                Diagonal = reader.GetDecimal(3),
                                TreeD = reader.GetBoolean(4),
                                Date = reader.GetDateTime(5),
                                Magazine = reader[6] as string
                            });
                    }
                }

                televisorsListing.TotalCount = (int)totalCount.Value % size == 0
                                                   ? (int)totalCount.Value / size
                                                   : ((int)totalCount.Value / size) + 1;

            }
            return televisorsListing;
        }

        public Televisor GetById(int televisorId)
        {
            var televisor = new Televisor();
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                var cmd = StoredProcedures.Televisors.GetById(conn, televisorId);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        televisor.Diagonal = reader.GetDecimal(0);
                        televisor.Name = reader[1] as string;
                        televisor.ReleaseDate = reader.GetDateTime(2);
                        televisor.TreeD = reader.GetBoolean(3);
                        televisor.Type = reader.GetInt32(4);
                        televisor.TelevisorId = reader.GetInt32(5);
                        televisor.Avatar = reader[6] as string;
                        televisor.MagazineId = reader.GetInt32(7);
                    }
                }
            }
            return televisor;
        }
    }
}
